*Load WHD FOIA datasets into stata 
*This do file loads the datasets received from the DOL Wage and Hours Division into Stata
*These datasets are saved in subfolder $whd

*Load FOIA "KASE" case-level data
import delimited "$whd/kase.txt", clear
*Label variables according to order in data dictionary (following DOL instructions)
local varnames CASE_ID CASE_STATUS_ID COMPLIANCE_ID INVESTIGATING_RO INVESTIGATING_DO INVESTIGATING_AO INVESTIGATING_FO DATE_BEG_INV_PER DATE_END_INV_PER DATE_CONCLUDED CONCLUDE_REASON_ID UNDUP_EES_COMPUTED UNDUP_EES_ASSESSED UNDUP_EES_VIOLATED TTL_CASE_VIOLATION AMT_BW_COMPUTED AMT_BW_ASSESSED TTL_BW_PD_TO_DATE TTL_CMP_PD_TO_DATE AMT_LD_ASSESSED AMT_BW_RESOLVED AMT_CMP_RESOLVEDDATE_REGISTERED INVEST_TOOL_ID RECURR_VIOL REINVESTIGATION REPEAT_VIOLATION REGISTRATION_ACT

local i 0
foreach varname of local varnames { 
	local i = `i' + 1
	rename v`i' `varname'
}
rename v28 ACT_ID
*Make sure that LD assessed can never be more than BW assessed - change made after querying DOL WHD official, who recommended this edit.
replace AMT_LD_ASSESSED = AMT_BW_ASSESSED if AMT_LD_ASSESSED>AMT_BW_ASSESSED & !missing(AMT_LD_ASSESSED)
save "$whd/kase.dta", replace

*Load FOIA CMP assessment case-level data
import delimited "$whd/case_cmp_assmnt.txt", clear
*Label variables according to order in data dictionary (following DOL instructions)
local varnames CASE_ID ACT_ID DATE_ASSMNT_LTR AMT_ASSMNT_LTR_ER
local i 0
foreach varname of local varnames { 
	local i = `i' + 1
	rename v`i' `varname'
}
*Keep only FLSA violations
keep if ACT_ID=="FLSA"
save "$whd/case_cmp_assmnt.dta", replace

*Load FOIA violation-level data
*Open file
import delimited "$whd/case_act_eer_viol.txt", clear
*Label variables according to order in data dictionary (following DOL instructions)
local varnames CASE_ID ACT_ID CASE_EER_ID VIOLATION_ID HAZ_OCCP_ID ACT_SPECIFIC_INFO ER_EE_VIOL VIOLATION_TYPE DATE_BEG_VIOL DATE_END_VIOL CMP_AMT_AT_INVEST AMT_BW_COMPUTED AMT_BW_ASSESSED AMT_LD_ASSESSED EE_AGE_AT_VIOL INJURY_VIOLATION INJURY_LEVEL_ID DATE_OF_INJURY AGE_AT_INJURY ACCIDENT_REPORT TIME_LOST_ON_JOB NUM_EES_REPRESENTD OTHER_EES_INVOLVED OTH_COMPLY_ACTIONS COMPLIANCE_STATUS COMPLY_REASON_ID VIOL_OCCURANCE CORRECTED_VIOL OSHA_COMBINED REASON_ID
local i 0
foreach varname of local varnames { 
	local i = `i' + 1
	rename v`i' `varname'
}
*Keep only FLSA violations
keep if ACT_ID=="FLSA"
save "$whd/case_act_eer_viol.dta", replace


*Load FOIA violation details data
*Open file
import delimited "$whd/v_act_violations.txt", clear
*Label variables according to order in data dictionary (following DOL instructions)
local varnames VIOLATION_ID ACT_ID VIOLATION_NO VIOLATION_TYPE VIOLATION_DESC EE_ER_VIOL START_AGE END_AGE FLAG_COMPUTE_FB ACTIVE_FLAG
local i 0
foreach varname of local varnames { 
	local i = `i' + 1
	rename v`i' `varname'
}
save "$whd/v_act_violations.dta", replace



*v_act_viol_codes
*Open file
import delimited "$whd/v_act_viol_codes.txt", clear
*Label variables according to order in data dictionary (following DOL instructions)
local varnames VIOL_CODE ACT_ID VIOL_CODE_DESC SORT_ORDER ACTIVE_FLAG
local i 0
foreach varname of local varnames { 
	local i = `i' + 1
	rename v`i' `varname'
}
save "$whd/v_act_viol_codes.dta", replace



*case_act_eer_sum
*Open file
import delimited "$whd/case_act_eer_sum.txt", clear
*Label variables according to order in data dictionary (following DOL instructions)
local varnames CASE_ID ACT_ID CASE_EER_ID ER_EE_VIOL TTL_ACT_VIOLATIONS AMT_BW_COMPUTED AMT_BW_ASSESSED AMT_LD_ASSESSED TTL_BW_PAID TTL_CMP_PAID RESOLUTION_DATE RESOLUTION_EE_CNT RESOLUTION_BW_AMT RESOLUTION_CMP_AMT DATE_BW_PAID DATE_CMP_PAID FLAG_INCLUDE DATE_CHANGED
local i 0
foreach varname of local varnames { 
	local i = `i' + 1
	rename v`i' `varname'
}
*Keep only FLSA violations
keep if ACT_ID=="FLSA"
save "$whd/case_act_eer_sum.dta", replace


*kase
*Open file
import delimited "$whd/kase.txt", clear
*Label variables according to order in data dictionary (following DOL instructions)
local varnames CASE_ID CASE_STATUS_ID COMPLIANCE_ID INVESTIGATING_RO INVESTIGATING_DO INVESTIGATING_AO INVESTIGATING_FO DATE_BEG_INV_PER DATE_END_INV_PER DATE_CONCLUDED CONCLUDE_REASON_ID UNDUP_EES_COMPUTED UNDUP_EES_ASSESSED UNDUP_EES_VIOLATED TTL_CASE_VIOLATION AMT_BW_COMPUTED AMT_BW_ASSESSED TTL_BW_PD_TO_DATE TTL_CMP_PD_TO_DATE AMT_LD_ASSESSED AMT_BW_RESOLVED AMT_CMP_RESOLVEDDATE_REGISTERED INVEST_TOOL_ID RECURR_VIOL REINVESTIGATION REPEAT_VIOLATION REGISTRATION_ACT

local i 0
foreach varname of local varnames { 
	local i = `i' + 1
	rename v`i' `varname'
}

save "$whd/kase.dta", replace


*case_act_summary
*Open file
import delimited "$whd/case_act_summary.txt", clear
*Label variables according to order in data dictionary (following DOL instructions)
local varnames CASE_ID ACT_ID TTL_ACT_VIOLATIONS DATE_FINDINGS_SUB FINDINGS_TOTAL_EES FINDINGS_EE_CNT AGRD_TO_DATE AGRD_TO_EES_CNT AMT_BW_COMPUTED AMT_BW_ASSESSED AMT_BW_PD_TO_DATE AMT_LD_ASSESSED AMT_CMP_PD_TO_DATE DATE_RESOLUTION RESOLUTION_EE_CNT RESOLUTION_BW_AMT RESOLUTION_CMP_AMT RESOLVE_REASON_ID COMPLIANCE_ID PAID_TO_DATE PAID_EES_CNT DATE_SYS_UPDATE FLAG_DETAIL_SUM
local i 0
foreach varname of local varnames { 
	local i = `i' + 1
	rename v`i' `varname'
}
*Keep only FLSA violations
keep if ACT_ID=="FLSA"
save "$whd/case_act_summary.dta", replace

*case_employer
*Open file
import delimited "$whd/case_employer.txt", clear
*Label variables according to order in data dictionary (following DOL instructions)
local varnames CASE_ID ER_TRADE_NAME ER_TRADE_SOUNDEX ER_LEGAL_NAME ER_LEGAL_SOUNDEX ER_ADDR1 ER_ADDR2 ER_ADDR3 ER_CITY ER_STATE_ID ER_ZIP ER_PHONE1 ER_PHONE2 ER_FAX ER_FAX_EXT ER_COUNTY_ID ER_TYPE_ID ER_BUS_ORG ER_NAICS ER_SIC_CODE ER_NATURE_OF_BUS ER_IC_COVERAGE_ID ER_NUM_OF_EES ER_NUM_EE_AFFECTED ER_NUM_BRANCHES ER_BR_OUTSIDE_AO  ER_BNPI PENALTY_TYPE EST_TYPE_ID EST_STATUS_ID EST_CERT_TYPE SPEC_COVERAGE_ID WORKSHOP_TYPE TTL_EES_CURRENT_YR TTL_EES_PRECEDE_YR ST_LOCAL_GOVT ER_PERM_ADDR1 ER_PERM_ADDR2 ER_PERM_ADDR3 ER_PERM_CITY ER_PERM_STATE_ID ER_PERM_ZIP ER_PERM_PHONE1 ER_PERM_PHONE2 ER_PERM_INTNTL_PH ER_PERM_FAX ER_PERM_FAX_EXT ER_PERM_COUNTY_ID LENG_LOCAL_STAY ER_AGENCY_TYPE ENDING_DAY_OF_WEEK DATE_BUS_OPENED
local i 0
foreach varname of local varnames { 
	local i = `i' + 1
	rename v`i' `varname'
}
*Prepare CASE_ID for later merging
gen new = CASE_ID
replace new = subinstr(new," ","",.)
drop CASE_ID
rename new CASE_ID
gen byte notnumeric = real(CASE_ID)==. /*makes indicator for obs w/o numeric values*/
tab notnumeric /*==1 where nonnumeric characters*/
list CASE_ID if notnumeric==1 /*will show which have nonnumeric*/
drop if notnumeric==1 /*drops 3 observations with incorrect data entry*/
destring CASE_ID, replace
save "$whd/case_employer.dta", replace


*case_cmp_assmnt
*Open file
import delimited "$whd/case_cmp_assmnt.txt", clear
*Label variables according to order in data dictionary (following DOL instructions)
local varnames CASE_ID ACT_ID DATE_ASSMNT_LTR AMT_ASSMNT_LTR_ER
local i 0
foreach varname of local varnames { 
	local i = `i' + 1
	rename v`i' `varname'
}
*Keep only FLSA violations
keep if ACT_ID=="FLSA"
save "$whd/case_cmp_assmnt.dta", replace




*v_viol_reason
*Open file
import delimited "$whd/v_viol_reason.txt", clear
*Label variables according to order in data dictionary (following DOL instructions)
local varnames REASON_ID ACT_ID REASON_DESC
local i 0
foreach varname of local varnames { 
	local i = `i' + 1
	rename v`i' `varname'
}
*Keep only FLSA violations
keep if ACT_ID=="FLSA"
save "$whd/v_viol_reason.dta", replace

*Generate dataset of cases with hot goods violations ONLY. This will be later merged into case level data
use "$whd/case_act_eer_viol.dta", clear
merge m:1 VIOLATION_ID using "$whd/v_act_violations.dta", gen(_m_viol)
replace VIOLATION_DESC = upper(VIOLATION_DESC)
keep if VIOLATION_ID == 1018
save "$whd/hot_goods.dta", replace


